package com.apexedu.eas.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.apexedu.eas.annotation.Query;
import com.apexedu.eas.entity.CommonPojo;
import com.apexedu.eas.entity.TjxPkxx;
import com.apexedu.eas.entity.TjxPkxxMx;
import com.apexedu.framework.util.Pager;
import com.apexedu.util.StringUtil;

@Repository
public class PkxxMxDao extends BasisDao<TjxPkxxMx, Long> {

	// 查询
	@Query(value = "select orgid from lbmember where roleid=(select id from lbrole where rolecode='PKCZR') and userid= ? ")
	public Object getOrgIdByUserId(String userId) {
		return null;
	}

	// 查询课程属性可选列表
	@Query(value = "select * from t_sjzx_dmjzx where ssdm='HB_KCSX' and zt=1")
	public List<Map<String, String>> getKcsx() {
		return null;
	}

	// 查询任课教师列表
	@Query("select distinct A.id,A.xm as caption,A.gh as des from TJX_JHJXBJS A LEFT JOIN TJX_JHJXB t on t.id=A.JXB  where  t.xq=?  ORDER BY A.ID")
	public List<CommonPojo> getRkjs(String xq) {
		return null;
	}

	@Query(value = "select id from t_xq where pkxq=1")
	public Object getCurrXqId() {
		return null;
	}

	// 拼接条件-行政班列表
	public List<CommonPojo> getXzb(String xq, String nj, String bjzzjg) {
		String sql = "select distinct A.id,A.xzbmc as caption  from (select s.id,s.xzbmc from TJX_JHJXB t "
				+ "left join T_BJ_GXXX04 s on instr(';'||t.bj||';',';'||s.id||';')>0" + " where 1=1" + " and t.xq = ?";
		if (StringUtil.isNotBlank(nj)) {
			sql += "and s.nj = " + nj;
		}
		if (StringUtil.isNotBlank(bjzzjg)) {
			sql += " and s.zzjg = " + bjzzjg;
		}
		sql += " order by t.id) A order by A.xzbmc asc";
		return easDBUtil.queryAllBeanList(sql, CommonPojo.class, xq, nj, bjzzjg);
	}

	// 教学班数量
	public int getCountJxb(String bj, String xq, String kclb, String zzjg, String cdxb, String sfypk, String sfytj,
			String ctzt, String kcmc, String gxjg0101, String rkjs, String nj, String jxblb, String jxbmc, String zy,
			String bjzzjg, String kcsx) {
		String sql = "  from TJX_JHJXB a" + " left join t_xn xn on xn.id=a.xn left join t_xq xq on xq.id=a.xq" + " " +
		// " left join T_BJ_GXXX04 k on
		// instr(';'||b.bj||';',';'||k.id||';')>0" +
		// " left join T_JS_GXJX0601 m on
		// instr(';'||b.gljs||';',';'||m.id||';')>0" +
		// " left join T_JS_GXJX0601 m on b.gljs=m.id " +
		// " left join TJX_JHZXJC n on n.zxjh=b.zxjx and n.kc=b.kc" +
		// -" left join t_sjzx_dmjzx e on e.ssdm='HB_JSLX' and a.hb_jslx=e.id"
				" left join t_sjzx_dmjzx h on h.ssdm='HB_KCSX' and a.HB_KCSX=h.id"
				+ " left join TJX_KC_GXJX02 z on z.id=a.kc"
				+ " left join t_sjzx_dmjzx i on i.ssdm='HB_KCXZ' and a.HB_KCXZ=i.id"
				// + " left join t_sjzx_dmjzx u on u.ssdm='XB_KHFS' and
				// z.kslx=u.id"
				+ " left join lborganization j on j.id=z.zzjg" + " where 1=1";

		if (!("-1".equals(bj) || "".equals(bj))) {
			sql += " and instr(';'||a.bj||';',';'||" + bj + "||';')>0";
		}
		sql += " and a.xq = " + xq;
		if (StringUtil.isNotBlank(kclb)) {
			if ("0".equals(kclb)) {
				sql += " and a.HB_KCSX not in (select id from t_sjzx_dmjzx where ssdm='HB_RKKCLB' and name like '％公共基础课％')";
			} else {
				sql += " and a.HB_KCSX = " + kclb;
			}
		}
		if (StringUtil.isNotBlank(zzjg)) {
			sql += " and a.zzjg = " + zzjg;
		}
		if (StringUtil.isNotBlank(cdxb)) {
			sql += " and b.cdxb = " + cdxb;
		}
		if (StringUtil.isNotBlank(sfypk)) {
			sql += " and a.sfypk = " + sfypk;
		}
		if (StringUtil.isNotBlank(ctzt)) {
			sql += " and a.ctzt = " + ctzt;
		}
		if (StringUtil.isNotBlank(kcmc)) {
			sql += " and a.kcmc like '%" + kcmc + "%'";
		}
		if (StringUtil.isNotBlank(gxjg0101)) {
			sql += " and a.jxbjsxm like '%" + gxjg0101 + "%'";
		}
		if (StringUtil.isNotBlank(sfytj)) {
			sql += " and a.sfytj = " + sfytj;
		}
		if (StringUtil.isNotBlank(rkjs)) {
			sql += " and (a.id in (select TJX_JHJXB_ID from TJX_JHJXB_GXJG0101_XX where gxjg0101 in (select id from GXJG0101 where xm like '%"
					+ rkjs + "%'))"
					+ " or a.id in (select JXB from TJX_PKXX_MX where CHECK_LS1(GXJG0101,(select to_char(replace(wm_concat(id),',',';')) from GXJG0101 where xm like '%"
					+ rkjs + "%'))=1))";
		}
		if (StringUtil.isNotBlank(nj)) {
			sql += " and a.nj=" + nj;
		}
		if (StringUtil.isNotBlank(jxblb)) {
			sql += " and a.jxblb = " + jxblb;
		}
		if (StringUtil.isNotBlank(jxbmc)) {
			sql += " and a.jxbmc like '%" + jxbmc + "%'";
		}
		if (StringUtil.isNotBlank(zy)) {
			sql += " and a.zy = " + zy;
		}
		if (StringUtil.isNotBlank(bjzzjg)) {
			sql += " and CHECK_LS1(a.bj,(select replace(wm_concat(id),',',';') from T_BJ_GXXX04 where zzjg=" + bjzzjg
					+ "))>=1";
		}
		if (StringUtil.isNotBlank(kcsx)) {// 过滤课程属性
			sql += " and a.HB_KCXZ = " + kcsx;
		}

		return easDBUtil.count(sql);
		// return easDBUtil.queryAllBeanList(sql, CommonPojo.class);
	}

	// 获取教学班
	public List<TjxPkxx> getJxb(String bj, String xq, String kclb, String zzjg, String cdxb, String sfypk, String sfytj,
			String ctzt, String kcmc, String gxjg0101, String rkjs, String nj, String jxblb, String jxbmc, String zy,
			String bjzzjg, String kcsx, String pxfs, Pager pager) {
		String sql = "select a.id as id,( select KCMC from TJX_KC_GXJX02 WHERE id = a.kc) kcmc,h.mc as kcsx,i.mc as kclb,a.kc as kc,"
				+ "(case to_number(a.JXBLX) when 1 then '非合并班' when 2 then '合并班' when 3 then '拆分子班' end) as jxblbmc,"
				+ "a.jxbmc as jxbmc,a.jxbrs jxbrssx,a.zkss zks,a.wks," +
				// "n.ksz as ksz,n.jsz as jsz," +
				// "a.jxb," + // "a.jxb," +
				"a.SFPK," +
				// "(case b.jxblb when 1 then k.js else 0 end) as gljs,m.jsqc as
				// gljsmc,z.zzjg as zzjg,j.name as zzjgmc,u.name as
				// khfsmc,b.hbqk," +
				// "(case b.jxblb when 1 then k.js else 0 end) as gljs,m.jsmc as
				// gljsmc,m.zws as gljszws,z.zzjg as zzjg,j.name as
				// zzjgmc,u.name as khfsmc,b.hbqk," +

				// m.id as gljs,m.jsmc as gljsmc,m.zws as gljszws,
				" z.zzjg as zzjg,j.name as zzjgmc,u.name as khfsmc,"
				+ "(select wmsys.wm_concat(c.xm) from TJX_JHJXBJS c where instr(';'||a.id||';',';'||c.jxb||';')>0) as jxbjsxm,"
				+
				// "(select wm_concat(p.id) from T_BJ_GXXX04 p where
				// instr(';'||b.bj||';',';'||p.id||';')>0 order by p.id asc) as
				// bj," +
				"a.bj as bj,a.FHBLX as fhbfs,"
				+ "(select wmsys.wm_concat(''''||o.id||''''||':'||''''||o.xzbmc||'''') from T_BJ_GXXX04 o where instr(';'||a.bj||';',';'||o.id||';')>0) as bjmc"
				+ "  from TJX_JHJXB a" + " left join t_xn xn on xn.id=a.xn left join t_xq xq on xq.id=a.xq" + " " +
				// " left join T_BJ_GXXX04 k on
				// instr(';'||b.bj||';',';'||k.id||';')>0" +
				// " left join T_JS_GXJX0601 m on
				// instr(';'||b.gljs||';',';'||m.id||';')>0" +
				// " left join T_JS_GXJX0601 m on b.gljs=m.id " +
				// " left join TJX_JHZXJC n on n.zxjh=b.zxjx and n.kc=b.kc" +
				// -" left join t_sjzx_dmjzx e on e.ssdm='HB_JSLX' and
				// a.hb_jslx=e.id"
				" left join t_sjzx_dmjzx h on h.ssdm='HB_KCSX' and a.HB_KCSX=h.id"
				+ " left join TJX_KC_GXJX02 z on z.id=a.kc"
				+ " left join t_sjzx_dmjzx i on i.ssdm='HB_KCXZ' and a.HB_KCXZ=i.id"
				+ " left join t_sjzx_dmjzx u on u.ssdm='XB_KHFS' and z.HB_KCLX=u.id"
				+ " left join lborganization j on j.id=z.zzjg" + " where 1=1";

		if (!("-1".equals(bj) || "".equals(bj))) {
			sql += " and instr(';'||a.bj||';',';'||" + bj + "||';')>0";
		}
		sql += " and a.xq = " + xq;
		if (StringUtil.isNotBlank(kclb)) {
			if ("0".equals(kclb)) {
				sql += " and a.HB_KCSX not in (select id from t_sjzx_dmjzx where ssdm='HB_RKKCLB' and name like '％公共基础课％')";
			} else {
				sql += " and a.HB_KCSX = " + kclb;
			}
		}
		if (StringUtil.isNotBlank(zzjg)) {
			sql += " and a.zzjg = " + zzjg;
		}
		if (StringUtil.isNotBlank(cdxb)) {
			sql += " and b.cdxb = " + cdxb;
		}
		if (StringUtil.isNotBlank(sfypk)) {
			sql += " and a.SFPK = " + sfypk;
		}
		/*
		 * if (StringUtil.isNotBlank(ctzt)) { sql += " and a.ctzt = " + ctzt; }
		 */
		if (StringUtil.isNotBlank(kcmc)) {
			sql += " and a.kcmc like '%" + kcmc + "%'";
		}
		if (StringUtil.isNotBlank(gxjg0101)) {
			sql += " and a.jxbjsxm like '%" + gxjg0101 + "%'";
		}
		if (StringUtil.isNotBlank(sfytj)) {
			sql += " and a.sfytj = " + sfytj;
		}
		if (StringUtil.isNotBlank(rkjs)) {
			sql += " and (a.id in (select TJX_JHJXB_ID from TJX_JHJXB_GXJG0101_XX where gxjg0101 in (select id from GXJG0101 where xm like '%"
					+ rkjs + "%'))"
					+ " or a.id in (select JXB from TJX_PKXX_MX where CHECK_LS1(GXJG0101,(select to_char(replace(wm_concat(id),',',';')) from GXJG0101 where xm like '%"
					+ rkjs + "%'))=1))";
		}
		if (StringUtil.isNotBlank(nj)) {
			sql += " and a.nj=" + nj;
		}
		if (StringUtil.isNotBlank(jxblb)) {
			sql += " and a.JXBLX = " + jxblb;
		}
		if (StringUtil.isNotBlank(jxbmc)) {
			sql += " and a.jxbmc like '%" + jxbmc + "%'";
		}
		if (StringUtil.isNotBlank(zy)) {
			sql += " and a.zy = " + zy;
		}
		if (StringUtil.isNotBlank(bjzzjg)) {
			sql += " and CHECK_LS1(a.bj,(select replace(wm_concat(id),',',';') from T_BJ_GXXX04 where zzjg=" + bjzzjg
					+ "))>=1";
		}
		if (StringUtil.isNotBlank(kcsx)) {// 过滤课程属性
			sql += " and a.HB_KCXZ = " + kcsx;
		}
		if (StringUtil.isNotBlank(pxfs)) {
			if ("1".equals(pxfs)) {
				sql += " order by a.kc";
			} else if ("2".equals(pxfs)) {
				sql += " order by a.jxb";
			} else if ("3".equals(pxfs)) {
				sql += " order by a.gxjg0101 desc";
			} else if ("4".equals(pxfs)) {
				sql += " order by a.zzjg";
			} else {
				sql += " order by a.jxbmc";
			}
		} else {
			sql += " order by a.jxbmc";
		}
		return easDBUtil.queryPageBeanList(pager, sql, TjxPkxx.class);
	}

	//// 查询出所有的任课教师列表
	public List<CommonPojo> getAllRkjs(String xq, String jsxm, String ssyx, String jyz) {

		String rkjs_sql = "select a.id,a.xm||'('||a.gh||')' as caption,a.xm as des,0 as currid,"
				// + " (select h.RKZDMS from TJX_JSRKMSSZ h where h.xq=" + xq +
				// " and a.id=h.rkjs) as p1,"
				// + " (select h.SFYXCG from TJX_JSRKMSSZ h where h.xq=" + xq +
				// " and a.id=h.rkjs) as p2,"
				// + " (select h.CGHYXZDMS from TJX_JSRKMSSZ h where h.xq=" + xq
				// + " and a.id=h.rkjs) as p3,"
				// + " (select h.RKZDKSL from TJX_JSRKKSLSZ h where h.xq=" + xq
				// + " and a.id=h.rkjs) as p4,"
				// + " (select h.SFYXCG from TJX_JSRKKSLSZ h where h.xq=" + xq +
				// " and a.id=h.rkjs) as p5,"
				// + " (select h.CGHYXZDKSL from TJX_JSRKKSLSZ h where h.xq=" +
				// xq + " and a.id=h.rkjs) as p6, " +
				/*
				 * " (select count(*) from tjx_pkxx_mx m where instr(';'||m.gxjg0101||';',';'||a.id||';')>0 "
				 * + " and tjx_pkxx_id in (select id from TJX_JHJXB where xq="
				 * +xq+")) as p7 " +
				 * " (select count(*) from tjx_pkxx_mx m where instr(';'||m.gxjg0101||';',';'||a.id||';')>0 "
				 * + " and tjx_pkxx_id in (select id from TJX_JHJXB where xq="
				 * +xq+")) as p8 " +
				 */
				+ " SUM_MS_KSL(" + xq + ",a.id,1) as p7, " + " SUM_MS_KSL(" + xq + ",a.id,2) as p8, " + " SUM_MS_KSL("
				+ xq + ",a.id,3) as p9, " + " SUM_MS_KSL(" + xq + ",a.id,4) as p10, " + "fn_pk_pkysgz(1," + xq
				+ ",a.id) as bz" +

				" from gxjg0101 a where " + "(a.xm like '%" + jsxm + "%' or a.gh like '%" + jsxm + "%') ";
		if (StringUtil.isNotBlank(ssyx)) {
			rkjs_sql = rkjs_sql + " and zzjg = " + ssyx;
		}
		if (StringUtil.isNotBlank(jyz)) {
			rkjs_sql = rkjs_sql + " and ssjyz = " + jyz;
		}
		// 查询出所有的任课教师列表
		return easDBUtil.queryAllBeanList(rkjs_sql + " ORDER BY nlssort(a.xm, 'NLS_SORT=SCHINESE_PINYIN_M')",
				CommonPojo.class);

	}

	//// 查询排课明细信息
	@Query("select zc as id,sfdsz as caption,(xq1||','||xq2||','||xq3||','||xq4||','||xq5||','||xq6||','||xq7) as des,gxjg0101 as superid from tjx_pkxx_mx where id= ? ")
	public CommonPojo getPkxxMx(String mxid) {
		return null;
	}

	//// 查询已经安排的任课教师
	public List<CommonPojo> getYapRkjs(String xq, String ssyx, String jyz, String jsxm) {
		String rkjs_ids = "select wm_concat(a.id) from gxjg0101 a where " + "(a.xm like '%" + jsxm
				+ "%' or a.gh like '%" + jsxm + "%') and sfqy=1";

		if (StringUtil.isNotBlank(ssyx)) {
			rkjs_ids = rkjs_ids + " and zzjg = " + ssyx;
		}
		if (StringUtil.isNotBlank(jyz)) {
			rkjs_ids = rkjs_ids + " and ssjyz = " + jyz;
		}
		String sql = "select mx.zc as id,mx.sfdsz as caption,"
				+ " (mx.xq1||','||mx.xq2||','||mx.xq3||','||mx.xq4||','||mx.xq5||','||mx.xq6||','||mx.xq7) as des,"
				+ " mx.gxjg0101 as superid,'0' as currid" + " from tjx_pkxx_mx mx"
				+ " left join TJX_JHJXB pk on mx.JXB=pk.id" + " where pk.xq = " + xq
				+ " and  ('%,'||mx.gxjg0101||',%' like '%,'||(" + rkjs_ids + ")||',%' " + " or '%,'||(" + rkjs_ids
				+ ")||',%' like '%,'||mx.gxjg0101||',%')";
		return easDBUtil.queryAllBeanList(sql, CommonPojo.class);

	}

	public List<TjxPkxxMx> getListPkmx(String pkxxid) {
		String pkmx_sql = "";
		pkmx_sql += " select mx.ID as id,";// -- 明细id
		pkmx_sql += " mx.GXJG0101 as ls,"; // --老师ids(多值对象)
		// pkmx_sql += " mx.tdqtjs as teamOtherTeacher,";// --团队其他老师(多值对象)
		pkmx_sql += " mx.JS as js,";// --教室ids";
		pkmx_sql += " mx.ZC as zc,";// --周次
		pkmx_sql += " mx.SFDSZ as sfdsz,";// --是否单双周
		pkmx_sql += " mx.XQ1 as xq1,";
		pkmx_sql += " mx.XQ2 as xq2,";
		pkmx_sql += " mx.XQ3 as xq3,";
		pkmx_sql += " mx.XQ4 as xq4,";
		pkmx_sql += " mx.XQ5 as xq5,";
		pkmx_sql += " mx.XQ6 as xq6,";
		pkmx_sql += " mx.XQ7 as xq7,";
		pkmx_sql += " mx.JXB as tjxPkxxId,";
		pkmx_sql += " (select tjs1_.JSMC from T_JS_GXJX0601 tjs1_ where tjs1_.ID = mx.JS) as jsName";// --教室名称
		pkmx_sql += " ,(select tjs2_.ZWS from T_JS_GXJX0601 tjs2_ where tjs2_.ID = mx.JS) as zws";// --教室座位数
		pkmx_sql += " ,(select bz from TJX_JHJXB where id=mx.JXB) as bz";
		pkmx_sql += " from TJX_PKXX_MX mx";
		pkmx_sql += " where mx.jxb in (" + pkxxid + ")";
		pkmx_sql += " order by mx.ID";

		return easDBUtil.queryAllBeanList(pkmx_sql, TjxPkxxMx.class);

	}

	public List<CommonPojo> getListLs(String pkxxid) {
		// 教学班教师集合
		String sql_jslb = "select distinct t.id as id,t.xm as caption,t.JXB as superid, " + " '' as p1," + " '' as p2,"
				+ " '' as p3," + " '' as p4," + " '' as p5," + " '' as p6, " + " SUM_MS_KSL(s.xq,t.id,1) as p7, "
				+ " SUM_MS_KSL(s.xq,t.id,2) as p8, " + " SUM_MS_KSL(s.xq,t.id,3) as p9,  "
				+ " SUM_MS_KSL(s.xq,t.id,4) as p10  " + " from " + " (select a.id,a.xm,g.JXB from TJX_PKXX_MX g"
				+ "  left join GXJG0101 a on instr(';'||g.gxjg0101||';',';'||a.id||';')>0 where g.gxjg0101 is not null) t"
				+ " left join TJX_JHJXB s on s.id=t.JXB" + " where t.JXB in (" + pkxxid + ") order by t.JXB,t.id";
		return easDBUtil.queryAllBeanList(sql_jslb, CommonPojo.class);
	}

	public List<CommonPojo> getListXzb(String pkxxid) {
		return easDBUtil.queryAllBeanList("select s.id as id,s.xzbmc as caption,a.id as superid " + " from TJX_JHJXB a "
				+ " left join T_BJ_GXXX04 s on instr(';'||a.bj||';',';'||s.id||';')>0" + " where a.id in (" + pkxxid
				+ ") order by a.id", CommonPojo.class);
	}

	@Query(value = "pro_pkxx_check(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", procOut = true)
	public List<Object> callPkxxCheck(String dlyhid, String pkmxid, String ksz, String jsz, String sfdsz, String jgid,
			String jsid, String xq1, String xq2, String xq3, String xq4, String xq5, String xq6, String xq7) {
		return null;
	}

	@Query("select fnextid('TJX_PKXX_MX') from dual")
	public Object getPkxxMxIdByFnextid() {
		return null;
	}

	@Query(value = "delete from TJX_JHJXBJS where jxb=?", update = true)
	public Integer deleteJxbjsByJxb(Long tjxPkxxId) {
		return null;
	}

	@Query(value = "procg_crjxbjs(?,?)", procOut = true)
	public void procCrjxbjs(Long tjxPkxxId, String bxjs) {
		/*final String [] params ={"","",""};
		final String queryString="";
		baseDao.getCurrentSession().doWork(new Work() {
			@Override
			public void execute(Connection conn) throws SQLException {
				ResultSet rs = null;
				CallableStatement call = conn.prepareCall("{" + queryString + "}");
				if (null != params) {
					for (int i = 0; i < params.length; i++) {
						call.setObject(i + 1, params[i]);
					}
				}
				rs = call.executeQuery();
				call.close();
				rs.close();

			}
		});*/

	}

	@Query(value = "select count(*) from TJX_PKXX_MX a where exists (select null from TJX_PKXX_MX b where a.JXB=b.JXB and b.id=?)", count = true)
	public Long getPkxxMxCountById(String id) {
		return null;
	}

	@Query(value = "update TJX_JHJXB set sfypk=0 where id in (select JXB from TJX_PKXX_MX where id=?)", update = true)
	public Integer updateJhjxbById(String id) {
		return null;
	}

	@Query("select JXB from TJX_PKXX_MX where id=?")
	public Object getJhjxbIdByMxId(String id) {
		return null;
	}

	@Query(value = "delete from TjxPkxxMx where id=?", update = true, nativeQuery = false)
	public Integer deletePkxxMxById(String id) {
		return null;
	}

	@Query(value = "select to_char(replace(wm_concat(distinct gxjg0101),',',';')) from TJX_PKXX_MX where JXB=?")
	public Object getRkjsidsByJxbId(String pkgcxxid) {
		return null;
	}
	public static void main(String[] args) {
		System.out.println(UUID.randomUUID().toString().replaceAll("-", ""));
	}
}
